﻿//
// (C) Copyright 2009 Irantha Suwandarathna (irantha@gmail.com)
// All rights reserved.
//

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using EffiProzDB;
using EffiProzDB.Lib;

namespace EffiProzDBTest
{
    /// <summary>
    /// Summary description for TransactionTest
    /// </summary>
    [TestClass]
    public class TransactionTest
    {
        SessionInterface sessionProxy;
        String user = "sa";
        String password = "";
        String connType = DatabaseURL.S_MEM;
        //String host = "localhost";
       // int port = 0;
       // String path = "Test";
        String database = "Test";
       // string sql = "";
        HsqlProperties props = new HsqlProperties();

        public TransactionTest()
        {
            sessionProxy = DatabaseManager.newSession(connType, database,
                      user, password, props);       
        }

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

        [TestMethod]
        public void Isolation()
        {
            //NO TX Isolation
            Result resultIn;

            SessionInterface sessionProxy2 = DatabaseManager.newSession(connType, database,
                     user, password, props);

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 int); "; // not allow null for col1
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "SET AUTOCOMMIT  FALSE;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 67;
            int col2val = 5;
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            col1val = 100;
            col2val = 9999;
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);  // uncommited changes visible in same session
            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, col2val);


            sql = "SET AUTOCOMMIT  FALSE;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy2, sql);

            col1val = 11100;
            col2val = 19999;
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy2, sql);

            sql = "DELETE FROM " + table + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy2, sql);  // uncommited changes visible in same session

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 0);


           

        }

        [TestMethod]
        public void CommitRollback()
        {
            Result resultIn;

            SessionInterface sessionProxy2 = DatabaseManager.newSession(connType, database,
                     user, password, props);

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 int); "; // not allow null for col1
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "SET AUTOCOMMIT  FALSE;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 67;
            int col2val = 5;
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            col1val = 100;
            col2val = 9999;
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);  // uncommited changes visible in same session
            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, col2val);

            sql = "ROLLBACK;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 0);  // rollback inserts        

             col1val = 167;
             col2val = 565;
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            col1val = 1007;
            col2val = 99998;
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "COMMIT;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "ROLLBACK WORK;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);  // commited inserts  

        }

        [TestMethod]
        public void SavePoint()
        {
            Result resultIn;
           
            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 int); "; // not allow null for col1
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "SET AUTOCOMMIT  FALSE;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 67;
            int col2val = 5;
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            col1val = 100;
            col2val = 9999;
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "SAVEPOINT sp11124;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            col1val = 167;
            col2val = 565;
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            col1val = 1007;
            col2val = 99998;
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 4);  // uncommited changes visible 

            sql = "ROLLBACK TO SAVEPOINT sp11124;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);  // rollback 2 inserts   

        }
    }
}
